create table Khach_hang
(
	Makh int identity(1,1),
	Tenkh nvarchar(255),
	Taikhoan nvarchar(255),
	Matkhau nvarchar(255),
	Sodt nvarchar(11),
	Email nvarchar(255),
	Diachi nvarchar(255),
	primary key (Makh)
)
------------INSERT INTO-----------
CREATE PROCEDURE dbo.Khach_hang_Insert
@Tenkh nvarchar(255),
@Taikhoan nvarchar(255),
@Matkhau nvarchar(255),
@Sodt nvarchar(11),
@Email nvarchar(255),
@Diachi nvarchar(255)
AS
INSERT INTO Khach_hang VALUES(
@Tenkh,
@Taikhoan,
@Matkhau,
@Sodt,
@Email,
@Diachi
)
RETURN
--------------UPDATE-----
CREATE PROCEDURE dbo.Khach_hang_Update
@Makh Int,
@Tenkh nvarchar(255),
@Taikhoan nvarchar(255),
@Matkhau nvarchar(255),
@Sodt nvarchar(11),
@Email nvarchar(255),
@Diachi nvarchar(255)
AS
UPDATE Khach_hang SET
Tenkh = @Tenkh,
Taikhoan = @Taikhoan,
Matkhau = @Matkhau,
Sodt = @Sodt,
Email = @Email,
Diachi = @Diachi
WHERE  Makh = @Makh
RETURN
--------DROP----
drop procedure dbo.Khach_hang_Update

--------------DELETE--------------
CREATE PROCEDURE dbo.Khach_hang_Delete
@Makh int
AS
declare @tong Int
select @tong = count (*) from Don_hang where Makh = @Makh
if @tong = 0
begin
delete from Khach_hang where Makh = @Makh
end
RETURN
--------------------------GET-----
CREATE PROCEDURE dbo.Khach_hang_Get
@Where NVARCHAR(255),
@PageIndex INT,
@PageSize INT,
@OrderBy NVARCHAR(25),
@OrderDirection NVARCHAR(4),
@TotalRecords INT OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
CREATE TABLE #Temp (
IDTemp INT IDENTITY(1,1),
Makh INT
)
DECLARE @sql NVARCHAR(1000)
SET @sql = 'INSERT INTO #temp ([Makh]) SELECT [Makh] FROM Khach_hang ' + @Where + ' ORDER BY ' + @OrderBy + ' ' + @OrderDirection
EXEC (@sql)
SELECT @TotalRecords = COUNT(*) FROM #Temp
SELECT
	k.Makh,
	k.Tenkh,
	k.Taikhoan,
	k.Matkhau,
	k.Sodt,
	k.Email,
	k.Diachi
FROM #Temp AS T JOIN Khach_hang AS k ON T.Makh=k.Makh
WHERE
(@PageIndex = 0)
OR
(T.IDTemp > (@PageIndex - 1) * @PageSize AND
T.IDTemp <= @PageIndex * @PageSize)
DROP TABLE #temp
-----------------------------
create table Nhan_vien
(
	Manv int identity(1,1),
	Tennv nvarchar(50),
	Gioitinh bit,
	Tendangnhap nvarchar(30),
	Matkhau nvarchar(50),
	Email nvarchar(100),
	Sodienthoai char(15),
primary key (Manv)
)
---------------INSERT INTO -----
CREATE PROCEDURE dbo.Nhan_vien_Insert
@Tennv nvarchar(50),
@Gioitinh bit,
@Tendangnhap nvarchar(30),
@Matkhau nvarchar(50),
@Email nvarchar(100),
@Sodienthoai char(15)
AS
INSERT INTO Nhan_vien VALUES (
@Tennv,
@Gioitinh,
@Tendangnhap,
@Matkhau,
@Email,
@Sodienthoai
)
RETURN
-----------UPDATE----
CREATE PROCEDURE dbo.Nhan_vien_Update
	@Manv int,
	@Tennv nvarchar(50),
	@Gioitinh bit,
	@Tendangnhap nvarchar(30),
	@Matkhau nvarchar(50),
	@Email nvarchar(100),
	@Sodienthoai char(15)
AS
UPDATE Nhan_vien SET
Tennv = @Tennv,
Gioitinh = @Gioitinh,
Tendangnhap = @Tendangnhap,
Matkhau = @Matkhau,
Email = @Email,
Sodienthoai = @Sodienthoai
WHERE Manv=@Manv
RETURN
--------DROP----
drop procedure dbo.Nhan_vien_Update
-------------DELETE----------
CREATE PROCEDURE dbo.Nhan_vien_Delete
@Manv int
AS
declare @tong Int
select @tong = count (*) from San_pham where Manv = @Manv
if @tong = 0
begin
delete from Nhan_vien where Manv = @Manv
end
RETURN
-------------------------------GET----------
CREATE PROCEDURE dbo.Nhan_vien_Get
@Where NVARCHAR(255),
@PageIndex INT,
@PageSize INT,
@OrderBy NVARCHAR(25),
@OrderDirection NVARCHAR(4),
@TotalRecords INT OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
CREATE TABLE #Temp (
IDTemp INT IDENTITY(1,1),
Manv INT
)
DECLARE @sql NVARCHAR(1000)
SET @sql = 'INSERT INTO #temp ([Manv]) SELECT [Manv] FROM Nhan_vien ' + @Where + ' ORDER BY ' + @OrderBy + ' ' + @OrderDirection
EXEC (@sql)
SELECT @TotalRecords = COUNT(*) FROM #Temp
SELECT
	n.Manv,
	n.Tennv,
	n.Gioitinh,
	n.Tendangnhap,
	n.Matkhau,
	n.Email,
	n.Sodienthoai
FROM #Temp AS T JOIN Nhan_vien AS n ON T.Manv=n.Manv
WHERE
(@PageIndex = 0)
OR
(T.IDTemp > (@PageIndex - 1) * @PageSize AND
T.IDTemp <= @PageIndex * @PageSize)
DROP TABLE #temp
--------------------------
create table Doanh_nghiep
(
	Madn int identity(1,1),
	Tendnghiep nvarchar(255),
	Dia_chi nvarchar(255),
	Matkhau nvarchar(255),
	Tendnhap nvarchar(255),
	Website nvarchar(255),
	primary key (Madn)
)
---------------INSERT INTO -----
CREATE PROCEDURE dbo.Doanh_nghiep_Insert
@Tendnghiep nvarchar(255),
@Dia_chi nvarchar(255),
@Matkhau nvarchar(255),
@Tendnhap nvarchar(255),
@Website nvarchar(255)
AS
INSERT INTO Doanh_nghiep VALUES (
@Tendnghiep,
@Dia_chi,
@Matkhau,
@Tendnhap,
@Website
)
RETURN
-----------UPDATE----
CREATE PROCEDURE dbo.Doanh_nghiep_Update
	@Madn int,
	@Tendnghiep nvarchar(255),
	@Dia_chi nvarchar(255),
	@Matkhau nvarchar(255),
	@Tendnhap nvarchar(255),
	@Website nvarchar(255)
AS
UPDATE Doanh_nghiep SET
Tendnghiep = @Tendnghiep,
Dia_chi = @Dia_chi,
Matkhau = @Matkhau,
Tendnhap = @Tendnhap,
Website = @Website
WHERE Madn=@Madn
RETURN
--------DROP----
drop procedure dbo.Doanh_nghiep_Update
-------------DELETE----------
CREATE PROCEDURE dbo.Doanh_nghiep_Delete
@Madn int
AS
declare @tong Int
select @tong = count (*) from San_pham where Madn = @Madn
if @tong = 0
begin
delete from Doanh_nghiep where Madn = @Madn
end
RETURN
-------------------------------GET----------
CREATE PROCEDURE dbo.Doanh_nghiep_Get
@Where NVARCHAR(255),
@PageIndex INT,
@PageSize INT,
@OrderBy NVARCHAR(25),
@OrderDirection NVARCHAR(4),
@TotalRecords INT OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
CREATE TABLE #Temp (
IDTemp INT IDENTITY(1,1),
Madn INT
)
DECLARE @sql NVARCHAR(1000)
SET @sql = 'INSERT INTO #temp ([Madn]) SELECT [Madn] FROM Doanh_nghiep ' + @Where + ' ORDER BY ' + @OrderBy + ' ' + @OrderDirection
EXEC (@sql)
SELECT @TotalRecords = COUNT(*) FROM #Temp
SELECT
	d.Madn,
	d.Tendnghiep,
	d.Dia_chi,
	d.Matkhau,
	d.Tendnhap,
	d.Website
FROM #Temp AS T JOIN Doanh_nghiep AS d ON T.Madn=d.Madn
WHERE
(@PageIndex = 0)
OR
(T.IDTemp > (@PageIndex - 1) * @PageSize AND
T.IDTemp <= @PageIndex * @PageSize)
DROP TABLE #temp
--------------------------
create table Danh_muc
(
	Madanhm int identity(1,1),
	Tendanhm nvarchar(255),
	primary key (Madanhm)
)
---------------INSERT INTO -----
CREATE PROCEDURE dbo.Danh_muc_Insert
@Tendanhm nvarchar(255)
AS
INSERT INTO Danh_muc VALUES (
@Tendanhm
)
RETURN
-----------UPDATE----
CREATE PROCEDURE dbo.Danh_muc_Update
	@Madanhm int,
	@Tendanhm nvarchar(255)
AS
UPDATE Danh_muc SET
Tendanhm = @Tendanhm
WHERE Madanhm=@Madanhm
RETURN
--------DROP----
drop procedure dbo.Danh_muc_Update
-------------DELETE----------
CREATE PROCEDURE dbo.Danh_muc_Delete
@Madanhm int
AS
declare @tong Int
select @tong = count (*) from San_pham where Madanhm = @Madanhm
if @tong = 0
begin
delete from Danh_muc where Madanhm = @Madanhm
end
RETURN
-------------------------------GET----------
CREATE PROCEDURE dbo.Danh_muc_Get
@Where NVARCHAR(255),
@PageIndex INT,
@PageSize INT,
@OrderBy NVARCHAR(25),
@OrderDirection NVARCHAR(4),
@TotalRecords INT OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
CREATE TABLE #Temp (
IDTemp INT IDENTITY(1,1),
Madanhm INT
)
DECLARE @sql NVARCHAR(1000)
SET @sql = 'INSERT INTO #temp ([Madanhm]) SELECT [Madanhm] FROM Danh_muc ' + @Where + ' ORDER BY ' + @OrderBy + ' ' + @OrderDirection
EXEC (@sql)
SELECT @TotalRecords = COUNT(*) FROM #Temp
SELECT
	dm.Madanhm,
	dm.Tendanhm
FROM #Temp AS T JOIN Danh_muc AS dm ON T.Madanhm=dm.Madanhm
WHERE
(@PageIndex = 0)
OR
(T.IDTemp > (@PageIndex - 1) * @PageSize AND
T.IDTemp <= @PageIndex * @PageSize)
DROP TABLE #temp
--------------------------
create table San_pham
(
	Masp int identity(1,1),
	Madn int ,
	Madanhm int ,
	Manv int,
	Makv int ,
	Madkien int,
	Mattsp int,
	Tensp nvarchar(255),
	Luotxem  int,
	Luotquangcao int,
	Dohapdan int,
	Soluong int,
	Giagoc int,
	Giaban int,
	Thoigianbd datetime,
	Thoigiankt datetime,
	Bando nvarchar(255),
	Tilechietkhau nvarchar(255),
	Mota text,
	primary key (Masp),
constraint fk_sanph_loaisp foreign key (Madanhm) references Danh_muc(Madanhm),
constraint fk_sanp_doanhn foreign key (Madn) references Doanh_nghiep(Madn),
constraint fk_sanp_khuv foreign key (Makv) references Khu_vuc(Makv),
constraint fk_sanp_dieuk foreign key (Madkien) references Dieu_kien(Madkien),
constraint fk_sanp_nhanv foreign key (Manv) references Nhan_vien(Manv),
constraint fk_sanp_ttsp foreign key (Mattsp) references Trangthai_sp(Mattsp)
)
---------------INSERT INTO -----
CREATE PROCEDURE dbo.San_pham_Insert
	@Madn int ,
	@Madanhm int ,
	@Manv int,
	@Makv int ,
	@Madkien int,
	@Mattsp int,
	@Tensp nvarchar(255),
	@Luotxem  int,
	@Luotquangcao int,
	@Dohapdan int,
	@Soluong int,
	@Giagoc int,
	@Giaban int,
	@Thoigianbd datetime,
	@Thoigiankt datetime,
	@Bando nvarchar(255),
	@Tilechietkhau nvarchar(255),
	@Mota text
AS
INSERT INTO San_pham VALUES (
	@Madn,
	@Madanhm,
	@Manv,
	@Makv,
	@Madkien,
	@Mattsp,
	@Tensp,
	@Luotxem,
	@Luotquangcao,
	@Dohapdan,
	@Soluong,
	@Giagoc,
	@Giaban,
	@Thoigianbd,
	@Thoigiankt,
	@Bando,
	@Tilechietkhau,
	@Mota
)
RETURN
-----------UPDATE----
CREATE PROCEDURE dbo.San_pham_Update
	@Masp int,
	@Madn int ,
	@Madanhm int ,
	@Manv int,
	@Makv int ,
	@Madkien int,
	@Mattsp int,
	@Tensp nvarchar(255),
	@Luotxem  int,
	@Luotquangcao int,
	@Dohapdan int,
	@Soluong int,
	@Giagoc int,
	@Giaban int,
	@Thoigianbd datetime,
	@Thoigiankt datetime,
	@Bando nvarchar(255),
	@Tilechietkhau nvarchar(255),
	@Mota text
AS
UPDATE San_pham SET
	Madn = @Madn,
	Madanhm = @Madanhm,
	Manv = @Manv,
	Makv = @Makv,
	Madkien = @Madkien,
	Mattsp = @Mattsp,
	Tensp = @Tensp,
	Luotxem = @Luotxem,
	Luotquangcao = @Luotquangcao,
	Dohapdan = @Dohapdan,
	Soluong = @Soluong,
	Giagoc = @Giagoc,
	Giaban = @Giaban,
	Thoigianbd = @Thoigianbd,
	Thoigiankt = @Thoigiankt,
	Bando = @Bando,
	Tilechietkhau = @Tilechietkhau,
	Mota = @Mota
WHERE Masp = @Masp
RETURN
--------DROP----
drop procedure dbo.San_pham_Update
-------------DELETE----------
CREATE PROCEDURE dbo.San_pham_Delete
@Masp int
AS
declare @tong Int
select @tong = count (*) from Chi_tiet_dh where Masp = @Masp
if @tong = 0
begin
delete from San_pham where Masp = @Masp
end
RETURN
-------------------------------GET----------
CREATE PROCEDURE dbo.San_pham_Get
@Where NVARCHAR(255),
@PageIndex INT,
@PageSize INT,
@OrderBy NVARCHAR(25),
@OrderDirection NVARCHAR(4),
@TotalRecords INT OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
CREATE TABLE #Temp (
IDTemp INT IDENTITY(1,1),
Masp INT
)
DECLARE @sql NVARCHAR(1000)
SET @sql = 'INSERT INTO #temp ([Masp]) SELECT [Masp] FROM San_pham ' + @Where + ' ORDER BY ' + @OrderBy + ' ' + @OrderDirection
EXEC (@sql)
SELECT @TotalRecords = COUNT(*) FROM #Temp
SELECT
	s.Masp,
	s.Madn,
	s.Madanhm,
	s.Manv,
	s.Makv,
	s.Madkien,
	s.Mattsp,
	s.Tensp,
	s.Luotxem,
	s.Luotquangcao,
	s.Dohapdan,
	s.Soluong,
	s.Giagoc,
	s.Giaban,
	s.Thoigianbd,
	s.Thoigiankt,
	s.Bando,
	s.Tilechietkhau,
	s.Mota
FROM #Temp AS T JOIN San_pham AS n ON T.Masp=s.Masp
WHERE
(@PageIndex = 0)
OR
(T.IDTemp > (@PageIndex - 1) * @PageSize AND
T.IDTemp <= @PageIndex * @PageSize)
DROP TABLE #temp
--------------------------
create table Trangthai_sp
(
	Mattsp int identity(1,1),
	Trangthai nvarchar(255),
	primary key (Mattsp)
)
---------------INSERT INTO -----
CREATE PROCEDURE dbo.Trangthai_sp_Insert
@Trangthai nvarchar(255)
AS
INSERT INTO Trangthai_sp VALUES (
@Trangthai
)
RETURN
-----------UPDATE----
CREATE PROCEDURE dbo.Trangthai_sp_Update
	@Mattsp int,
	@Trangthai nvarchar(255)
AS
UPDATE Trangthai_sp SET
Trangthai = @Trangthai
WHERE Mattsp=@Mattsp
RETURN
--------DROP----
drop procedure dbo.Trangthai_sp_Update
-------------DELETE----------
CREATE PROCEDURE dbo.Trangthai_sp_Delete
@Mattsp int
AS
declare @tong Int
select @tong = count (*) from San_pham where Mattsp = @Mattsp
if @tong = 0
begin
delete from Trangthai_sp where Mattsp = @Mattsp
end
RETURN
-------------------------------GET----------
CREATE PROCEDURE dbo.Trangthai_sp_Get
@Where NVARCHAR(255),
@PageIndex INT,
@PageSize INT,
@OrderBy NVARCHAR(25),
@OrderDirection NVARCHAR(4),
@TotalRecords INT OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
CREATE TABLE #Temp (
IDTemp INT IDENTITY(1,1),
Mattsp INT
)
DECLARE @sql NVARCHAR(1000)
SET @sql = 'INSERT INTO #temp ([Mattsp]) SELECT [Mattsp] FROM Trangthai_sp ' + @Where + ' ORDER BY ' + @OrderBy + ' ' + @OrderDirection
EXEC (@sql)
SELECT @TotalRecords = COUNT(*) FROM #Temp
SELECT
	ts.Mattsp,
	ts.rangthai
FROM #Temp AS T JOIN Trangthai_sp AS ts ON T.Mattsp=ts.Mattsp
WHERE
(@PageIndex = 0)
OR
(T.IDTemp > (@PageIndex - 1) * @PageSize AND
T.IDTemp <= @PageIndex * @PageSize)
DROP TABLE #temp
--------------------------
create table Luu_y
(
	Maluuy int identity(1,1),
	Phieudientu text,
	Cachinhthuctt text,
	Luuy text,
	primary key(Maluuy)
)
---------------INSERT INTO -----
CREATE PROCEDURE dbo.Luu_y_Insert
	@Phieudientu text,
	@Cachinhthuctt text,
	@Luuy text
AS
INSERT INTO Luu_y VALUES (
@Phieudientu,
@Cachinhthuctt,
@Luuy
)
RETURN
-----------UPDATE----
CREATE PROCEDURE dbo.Luu_y_Update
	@Maluuy int,
	@Phieudientu text,
	@Cachinhthuctt text,
	@Luuy text
AS
UPDATE Luu_y SET
Phieudientu = @Phieudientu,
Cachinhthuctt = @Cachinhthuctt,
Luuy = @Luuy
WHERE @Maluuy=@Maluuy
RETURN
--------DROP----
drop procedure dbo.Luu_y_Update
-------------DELETE----------
CREATE PROCEDURE dbo.Luu_y_Delete
@Maluuy int
AS
declare @tong Int
select @tong = count (*) from San_pham where Maluuy = @Maluuy
if @tong = 0
begin
delete from Luu_y where Maluuy = @Maluuy
end
RETURN
-------------------------------GET----------
CREATE PROCEDURE dbo.Luu_y_Get
@Where NVARCHAR(255),
@PageIndex INT,
@PageSize INT,
@OrderBy NVARCHAR(25),
@OrderDirection NVARCHAR(4),
@TotalRecords INT OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
CREATE TABLE #Temp (
IDTemp INT IDENTITY(1,1),
Maluuy INT
)
DECLARE @sql NVARCHAR(1000)
SET @sql = 'INSERT INTO #temp ([Maluuy]) SELECT [Maluuy] FROM Luu_y ' + @Where + ' ORDER BY ' + @OrderBy + ' ' + @OrderDirection
EXEC (@sql)
SELECT @TotalRecords = COUNT(*) FROM #Temp
SELECT
	l.Maluuy,
	l.Phieudientu,
	l.Cachinhthuctt,
	l.Luuy
FROM #Temp AS T JOIN Luu_y AS l ON T.Maluuy=l.Maluuy
WHERE
(@PageIndex = 0)
OR
(T.IDTemp > (@PageIndex - 1) * @PageSize AND
T.IDTemp <= @PageIndex * @PageSize)
DROP TABLE #temp
--------------------------
create table Dieu_kien
(
	Madkien int identity(1,1),
	Dieukien text,
	Hansudung datetime,
	Diachi nvarchar(255),
	Dienthoai nvarchar(11),
	Giomocua datetime,
	primary key(Madkien)
)
---------------INSERT INTO -----
CREATE PROCEDURE dbo.Dieu_kien_Insert
	@Dieukien text,
	@Hansudung datetime,
	@Diachi nvarchar(255),
	@Dienthoai nvarchar(11),
	@Giomocua datetime
AS
INSERT INTO Dieu_kien VALUES (
	@Dieukien,
	@Hansudung,
	@Diachi,
	@Dienthoai,
	@Giomocua
)
RETURN
-----------UPDATE----
CREATE PROCEDURE dbo.Dieu_kien_Update
	@Madkien int,
	@Dieukien text,
	@Hansudung datetime,
	@Diachi nvarchar(255),
	@Dienthoai nvarchar(11),
	@Giomocua datetime
AS
UPDATE Dieu_kien SET
	Dieukien = @Dieukien,
	Hansudung = @Hansudung,
	Diachi = @Diachi,
	Dienthoai = @Dienthoai,
	Giomocua = @Giomocua
WHERE Madkien=@Madkien
RETURN
--------DROP----
drop procedure dbo.Dieu_kien_Update
-------------DELETE----------
CREATE PROCEDURE dbo.Dien_kien_Delete
@Madkien int
AS
declare @tong Int
select @tong = count (*) from San_pham where Madkien = @Madkien
if @tong = 0
begin
delete from Dieu_kien where Madkien = @Madkien
end
RETURN
-------------------------------GET----------
CREATE PROCEDURE dbo.Dieu_kien_Get
@Where NVARCHAR(255),
@PageIndex INT,
@PageSize INT,
@OrderBy NVARCHAR(25),
@OrderDirection NVARCHAR(4),
@TotalRecords INT OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
CREATE TABLE #Temp (
IDTemp INT IDENTITY(1,1),
Madkien INT
)
DECLARE @sql NVARCHAR(1000)
SET @sql = 'INSERT INTO #temp ([Madkien]) SELECT [Madkien] FROM Dieu_kien ' + @Where + ' ORDER BY ' + @OrderBy + ' ' + @OrderDirection
EXEC (@sql)
SELECT @TotalRecords = COUNT(*) FROM #Temp
SELECT
	dk.Madkien,
	dk.Dieukien,
	dk.Hansudung,
	dk.Diachi,
	dk.Dienthoai,
	dk.Giomocua
FROM #Temp AS T JOIN Dieu_kien AS dk ON T.Madkien=dk.Madkien
WHERE
(@PageIndex = 0)
OR
(T.IDTemp > (@PageIndex - 1) * @PageSize AND
T.IDTemp <= @PageIndex * @PageSize)
DROP TABLE #temp
--------------------------
create table Hinh_sanp
(
	Mahinh int identity(1,1),
	Masp int,
	Tenhinh nvarchar(255),
	Duongdan nvarchar(255),
	primary key (Mahinh),
constraint fk_hinhsanp_sanp foreign key (Masp) references San_pham(Masp)
)
---------------INSERT INTO -----chua chay 
CREATE PROCEDURE dbo.Hinh_sanp_Insert
	@Masp int,
	@Tenhinh nvarchar(255),
	@Duongdan nvarchar(255)
AS
INSERT INTO Hinh_sanp VALUES (
	@Masp,
	@Tenhinh,
	@Duongdan
)
RETURN
-----------UPDATE----
CREATE PROCEDURE dbo.Hinh_sanp_Update
	@Mahinh int,
	@Masp int,
	@Tenhinh nvarchar(255),
	@Duongdan nvarchar(255)
AS
UPDATE Hinh_sanp SET
	Masp = @Masp,
	Tenhinh = @Tenhinh,
	Duongdan = @Duongdan

	WHERE Mahinh=@Mahinh
RETURN
--------DROP----
drop procedure dbo.Hinh_sanp_Update
-------------DELETE----------
CREATE PROCEDURE dbo.Hinh_sanp_Delete
@Mahinh int
AS
declare @tong Int
select @tong = count (*) from San_pham where Mahinh = @Mahinh
if @tong = 0
begin
delete from Hinh_sanp where Mahinh = @Mahinh
end
RETURN
-------------------------------GET----------
CREATE PROCEDURE dbo.Hinh_sanp_Get
@Where NVARCHAR(255),
@PageIndex INT,
@PageSize INT,
@OrderBy NVARCHAR(25),
@OrderDirection NVARCHAR(4),
@TotalRecords INT OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
CREATE TABLE #Temp (
IDTemp INT IDENTITY(1,1),
Mahinh INT
)
DECLARE @sql NVARCHAR(1000)
SET @sql = 'INSERT INTO #temp ([Mahinh]) SELECT [Mahinh] FROM Hinh_sanp ' + @Where + ' ORDER BY ' + @OrderBy + ' ' + @OrderDirection
EXEC (@sql)
SELECT @TotalRecords = COUNT(*) FROM #Temp
SELECT
	hs.Mahinh,
	hs.Masp,
	hs.Tenhinh,
	hs.Duongdan
FROM #Temp AS T JOIN Hinh_sanp AS hs ON T.Mahinh=hs.Mahinh
WHERE
(@PageIndex = 0)
OR
(T.IDTemp > (@PageIndex - 1) * @PageSize AND
T.IDTemp <= @PageIndex * @PageSize)
DROP TABLE #temp
--------------------------
create table Hinh_thuc_thanhtoan
(
	Matt int identity(1,1),
	Tenhttt nvarchar(255),
	Motathem nvarchar(255),
	primary key(Matt)
)
---------------INSERT INTO -----
CREATE PROCEDURE dbo.Hinh_thuc_thanhtoan_Insert
	@Tenhttt nvarchar(255),
	@Motathem nvarchar(255)
AS
INSERT INTO Hinh_thuc_thanhtoan VALUES (
	@Tenhttt,
	@Motathem
)
RETURN
-----------UPDATE----
CREATE PROCEDURE dbo.Hinh_thuc_thanhtoan_Update
	@Matt int,
	@Tenhttt nvarchar(255),
	@Motathem nvarchar(255)
AS
UPDATE Hinh_thuc_thanhtoan SET
	Tenhttt = @Tenhttt,
	Motathem = @Motathem
	
WHERE Matt=@Matt
RETURN
--------DROP----
drop procedure dbo.Hinh_thuc_thanhtoan_Update
-------------DELETE----------
CREATE PROCEDURE dbo.Hinh_thuc_thanhtoan_Delete
@Matt int
AS
declare @tong Int
select @tong = count (*) from Don_hang where Matt = @Matt
if @tong = 0
begin
delete from Hinh_thuc_thanhtoan where Matt = @Matt
end
RETURN
-------------------------------GET----------
CREATE PROCEDURE dbo.Hinh_thuc_thanhtoan_Get
@Where NVARCHAR(255),
@PageIndex INT,
@PageSize INT,
@OrderBy NVARCHAR(25),
@OrderDirection NVARCHAR(4),
@TotalRecords INT OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
CREATE TABLE #Temp (
IDTemp INT IDENTITY(1,1),
Matt INT
)
DECLARE @sql NVARCHAR(1000)
SET @sql = 'INSERT INTO #temp ([Matt]) SELECT [Matt] FROM Hinh_thuc_thanhtoan ' + @Where + ' ORDER BY ' + @OrderBy + ' ' + @OrderDirection
EXEC (@sql)
SELECT @TotalRecords = COUNT(*) FROM #Temp
SELECT
	ht.Matt,
	ht.Tenhttt,
	ht.Motathem
FROM #Temp AS T JOIN Hinh_thuc_thanhtoan AS ht ON T.Matt=ht.Matt
WHERE
(@PageIndex = 0)
OR
(T.IDTemp > (@PageIndex - 1) * @PageSize AND
T.IDTemp <= @PageIndex * @PageSize)
DROP TABLE #temp
--------------------------
create table Lien_ket
(
	Malienket int,
	Tenweb nvarchar(100),
	primary key (Malienket)
)
---------------INSERT INTO -----chua chay
CREATE PROCEDURE dbo.Lien_ket_Insert
	@Tenweb nvarchar(100)
AS
INSERT INTO Lien_ket VALUES (
	@Tenweb
)
RETURN
-----------UPDATE----
CREATE PROCEDURE dbo.Lien_ket_Update
	@Malienket int,
	@Tenweb nvarchar(100)
AS
UPDATE Lien_ket SET
	Tenweb = @Tenweb

	
WHERE Malienket=@Malienket
RETURN
--------DROP----
drop procedure dbo.Lien_ket_Update
-------------DELETE----------chua chay 
CREATE PROCEDURE dbo.Lien_ket_Delete
@Malienket int
AS
declare @tong Int
select @tong = count (*) from Don_hang where Malienket = @Malienket
if @tong = 0
begin
delete from Lien_ket where Malienket = @Malienket
end
RETURN
-------------------------------GET----------
CREATE PROCEDURE dbo.Lien_ket_Get
@Where NVARCHAR(255),
@PageIndex INT,
@PageSize INT,
@OrderBy NVARCHAR(25),
@OrderDirection NVARCHAR(4),
@TotalRecords INT OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
CREATE TABLE #Temp (
IDTemp INT IDENTITY(1,1),
Malienket INT
)
DECLARE @sql NVARCHAR(1000)
SET @sql = 'INSERT INTO #temp ([Malienket]) SELECT [Malienket] FROM Lien_ket ' + @Where + ' ORDER BY ' + @OrderBy + ' ' + @OrderDirection
EXEC (@sql)
SELECT @TotalRecords = COUNT(*) FROM #Temp
SELECT
	lk.Malienket,
	lk.Tenweb
FROM #Temp AS T JOIN Lien_ket AS lk ON T.Malienket=lk.Malienket
WHERE
(@PageIndex = 0)
OR
(T.IDTemp > (@PageIndex - 1) * @PageSize AND
T.IDTemp <= @PageIndex * @PageSize)
DROP TABLE #temp
--------------------------
create table Don_hang
(
	Madh int identity(1,1),
	Makh int ,
	Matt int ,
	Matinhtrangdh int,
	Ngaydathang datetime,
	Ngaygiaohang datetime,
	Diachi nvarchar(255),
	Trigia int,
	primary key (Madh)	,
constraint fk_chitetdh_kh foreign key (Makh) references Khach_hang(Makh),
constraint fk_chitietdh_httt foreign key (Matt) references Hinh_thuc_thanhtoan(Matt),
constraint fk_chitietdh_ttdh foreign key (Matinhtrangdh) references Trangthai_hdon(Matinhtrangdh)
)
---------------INSERT INTO -----
CREATE PROCEDURE dbo.Don_hang_Insert
	@Makh int ,
	@Matt int ,
	@Matinhtrangdh int,
	@Ngaydathang datetime,
	@Ngaygiaohang datetime,
	@Diachi nvarchar(255),
	@Trigia int
AS
INSERT INTO Don_hang VALUES (
	@Makh,
	@Matt,
	@Matinhtrangdh,
	@Ngaydathang,
	@Ngaygiaohang,
	@Diachi,
	@Trigia
)
RETURN
-----------UPDATE----
CREATE PROCEDURE dbo.Don_hang_Update
	@Madh int,
	@Makh int,
	@Matt int,
	@Matinhtrangdh int,
	@Ngaydathang datetime,
	@Ngaygiaohang datetime,
	@Diachi nvarchar(255),
	@Trigia int
AS
UPDATE Don_hang SET
	Makh = @Makh,
	Matt = @Matt,
	Matinhtrangdh = @Matinhtrangdh,
	Ngaydathang = @Ngaydathang,
	Ngaygiaohang = @Ngaygiaohang,
	Diachi = @Diachi,
	Trigia = @Trigia

WHERE Madh = @Madh
RETURN
--------DROP----
drop procedure dbo.Don_hang_Update
-------------DELETE----------
CREATE PROCEDURE dbo.Don_hang_Delete
@Madh int
AS
declare @tong Int
select @tong = count (*) from Chi_tiet_dh where Madh = @Madh
if @tong = 0
begin
delete from Don_hang where Madh = @Madh
end
RETURN
-------------------------------GET----------chua chay
CREATE PROCEDURE dbo.Don_hang_Get
@Where NVARCHAR(255),
@PageIndex INT,
@PageSize INT,
@OrderBy NVARCHAR(25),
@OrderDirection NVARCHAR(4),
@TotalRecords INT OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
CREATE TABLE #Temp (
IDTemp INT IDENTITY(1,1),
Madh INT
)
DECLARE @sql NVARCHAR(1000)
SET @sql = 'INSERT INTO #temp ([Madh]) SELECT [Madh] FROM Don_hang ' + @Where + ' ORDER BY ' + @OrderBy + ' ' + @OrderDirection
EXEC (@sql)
SELECT @TotalRecords = COUNT(*) FROM #Temp
SELECT
	dh.Madh,
	dh.Makh,
	dh.Matt,
	dh.Matinhtrangdh,
	dh.Ngaydathang,
	dh.Ngaygiaohang,
	dh.Diachi,
	dh.Trigia
FROM #Temp AS T JOIN Don_hang AS dh ON T.Madh=dh.Madh
WHERE
(@PageIndex = 0)
OR
(T.IDTemp > (@PageIndex - 1) * @PageSize AND
T.IDTemp <= @PageIndex * @PageSize)
DROP TABLE #temp
--------------------------
create table Chi_tiet_dh
(
	Mactdh int identity(1,1),
	Madh int ,
	Masp int,
	Soluong int,
	Dongia int,
primary key (Mactdh),
	--primary key (Madh,Masp),
--constraint fk_chitietdh_donh foreign key (Madh) references Don_hang(Madh)
--constraint fk_chitietdh_sap foreign key (Masp) references San_pham(Masp)
)
---------------INSERT INTO -----
CREATE PROCEDURE dbo.Chi_tiet_dh_Insert
	@Mactdh int,
	@Madh int ,
	@Masp int,
	@Soluong int,
	@Dongia int
AS
INSERT INTO Chi_tiet_dh VALUES (
	@Mactdh,
	@Madh,
	@Masp,
	@Soluong,
	@Dongia
)
RETURN
-----------UPDATE----chua chay
CREATE PROCEDURE dbo.Chi_tiet_dh_Update
	@Mactdh int,
	@Madh int ,
	@Masp int,
	@Soluong int,
	@Dongia int
AS
UPDATE Chi_tiet_dh SET
	Madh = @Madh,
	Masp = @Masp,
	Soluong = @soluong,
	Dongia = @Dongia
WHERE Mactdh=@Mactdh
RETURN
--------DROP----
drop procedure dbo.Chi_tiet_dh_Update
-------------DELETE----------
CREATE PROCEDURE dbo.Chi_tiet_dh_Delete
@Mactdh int
AS
declare @tong Int
select @tong = count (*) from San_pham where Mactdh = @Mactdh
if @tong = 0
begin
delete from Chi_tiet_dh where Mactdh = @Mactdh
end
RETURN
-------------------------------GET----------
CREATE PROCEDURE dbo.Chi_tiet_dh_Get
@Where NVARCHAR(255),
@PageIndex INT,
@PageSize INT,
@OrderBy NVARCHAR(25),
@OrderDirection NVARCHAR(4),
@TotalRecords INT OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
CREATE TABLE #Temp (
IDTemp INT IDENTITY(1,1),
Mactdh INT
)
DECLARE @sql NVARCHAR(1000)
SET @sql = 'INSERT INTO #temp ([Mactdh]) SELECT [Mactdh] FROM Chi_tiet_dh ' + @Where + ' ORDER BY ' + @OrderBy + ' ' + @OrderDirection
EXEC (@sql)
SELECT @TotalRecords = COUNT(*) FROM #Temp
SELECT
	c.Mactdh,
	c.Madh,
	c.Masp,
	c.Soluong,
	c.Dongia
FROM #Temp AS T JOIN Chi_tiet_dh AS c ON T.Mactdh=c.Mactdh
WHERE
(@PageIndex = 0)
OR
(T.IDTemp > (@PageIndex - 1) * @PageSize AND
T.IDTemp <= @PageIndex * @PageSize)
DROP TABLE #temp
--------------------------
create table Trangthai_hdon
(
	Matinhtrangdh int identity(1,1),
	Trangthai nvarchar(255),
primary key (Matinhtrangdh)
)
---------------INSERT INTO -----
CREATE PROCEDURE dbo.Trangthai_hdon_Insert
	@Trangthai nvarchar(255)
AS
INSERT INTO Trangthai_hdon VALUES (
	@Trangthai
)
RETURN
-----------UPDATE----
CREATE PROCEDURE dbo.Trangthai_hdon_Update
	@Matinhtrangdh int,
	@Trangthai nvarchar(255)
AS
UPDATE Trangthai_hdon SET
	Trangthai = @Trangthai
WHERE @Matinhtrangdh = @Matinhtrangdh
RETURN
--------DROP----
drop procedure dbo.Trangthai_hdon_Update
-------------DELETE----------
CREATE PROCEDURE dbo.Trangthai_hdon_Delete
@Matinhtrangdh int
AS
declare @tong Int
select @tong = count (*) from Don_hang where Matinhtrangdh = @Matinhtrangdh
if @tong = 0
begin
delete from Trangthai_hdon where Matinhtrangdh = @Matinhtrangdh
end
RETURN
-------------------------------GET----------
CREATE PROCEDURE dbo.Trangthai_hdon_Get
@Where NVARCHAR(255),
@PageIndex INT,
@PageSize INT,
@OrderBy NVARCHAR(25),
@OrderDirection NVARCHAR(4),
@TotalRecords INT OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
CREATE TABLE #Temp (
IDTemp INT IDENTITY(1,1),
Matinhtrangdh INT
)
DECLARE @sql NVARCHAR(1000)
SET @sql = 'INSERT INTO #temp ([Matinhtrangdh]) SELECT [Matinhtrangdh] FROM Trangthai_hdon ' + @Where + ' ORDER BY ' + @OrderBy + ' ' + @OrderDirection
EXEC (@sql)
SELECT @TotalRecords = COUNT(*) FROM #Temp
SELECT
	th.Matinhtrangdh,
	th.Trangthai
FROM #Temp AS T JOIN Trangthai_hdon AS th ON T.Matinhtrangdh=th.Matinhtrangdh
WHERE
(@PageIndex = 0)
OR
(T.IDTemp > (@PageIndex - 1) * @PageSize AND
T.IDTemp <= @PageIndex * @PageSize)
DROP TABLE #temp
--------------------------
create table Khu_vuc
(
	Makv int identity(1,1),
	Tenkv nvarchar(255),
	primary key (Makv)
) 
---------------INSERT INTO -----
CREATE PROCEDURE dbo.Khu_vuc_Insert
	@Tenkv nvarchar(255)
AS
INSERT INTO Khu_vuc VALUES (
	@Tenkv
)
RETURN
-----------UPDATE----
CREATE PROCEDURE dbo.Khu_vuc_Update
	@Makv int,
	@Tenkv nvarchar(255)
AS
UPDATE Khu_vuc SET
	Tenkv = @Tenkv
WHERE Makv = @Makv
RETURN
--------DROP----
drop procedure dbo.Khu_vuc_Update
-------------DELETE----------
CREATE PROCEDURE dbo.Khu_vuc_Delete
@Makv int
AS
declare @tong Int
select @tong = count (*) from San_pham where Makv = @Makv
if @tong = 0
begin
delete from Khu_vuc where Makv = @Makv
end
RETURN
-------------------------------GET----------
CREATE PROCEDURE dbo.Khu_vuc_Get
@Where NVARCHAR(255),
@PageIndex INT,
@PageSize INT,
@OrderBy NVARCHAR(25),
@OrderDirection NVARCHAR(4),
@TotalRecords INT OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
CREATE TABLE #Temp (
IDTemp INT IDENTITY(1,1),
Makv INT
)
DECLARE @sql NVARCHAR(1000)
SET @sql = 'INSERT INTO #temp ([Makv]) SELECT [Makv] FROM Khu_vuc ' + @Where + ' ORDER BY ' + @OrderBy + ' ' + @OrderDirection
EXEC (@sql)
SELECT @TotalRecords = COUNT(*) FROM #Temp
SELECT
	kv.Makv,
	kv.Tenkv
FROM #Temp AS T JOIN Khu_vuc AS kv ON T.Makv=kv.Makv
WHERE
(@PageIndex = 0)
OR
(T.IDTemp > (@PageIndex - 1) * @PageSize AND
T.IDTemp <= @PageIndex * @PageSize)
DROP TABLE #temp
--------------------------
create table Du_lich
(
	Madul int identity(1,1),
	Madden int,
	Masp int,
	primary key(Madul),
constraint fk_dulich_dden foreign key (Madden) references Diem_den(Madden),
constraint fk_deal_sap foreign key (Masp) references San_pham(Masp)
)
---------------INSERT INTO -----
CREATE PROCEDURE dbo.Du_lich_Insert
	@Madden int,
	@Masp int
AS
INSERT INTO Du_lich VALUES (
	@Madden,
	@Masp
)
RETURN
-----------UPDATE----
CREATE PROCEDURE dbo.Du_lich_Update
	@Madul int,
	@Madden int,
	@Masp int
AS
UPDATE Du_lich SET
	Madden = @Madden,
	Masp = @Masp
WHERE Madul = @Madul
RETURN
--------DROP----
drop procedure dbo.Du_lich_Update
-------------DELETE----------chua chay
CREATE PROCEDURE dbo.Du_lich_Delete
@Madul int
AS
declare @tong Int
select @tong = count (*) from San_pham where Madul = @Madul
if @tong = 0
begin
delete from Du_lich where Madul = @Madul
end
RETURN
-------------------------------GET----------
CREATE PROCEDURE dbo.Du_lich_Get
@Where NVARCHAR(255),
@PageIndex INT,
@PageSize INT,
@OrderBy NVARCHAR(25),
@OrderDirection NVARCHAR(4),
@TotalRecords INT OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
CREATE TABLE #Temp (
IDTemp INT IDENTITY(1,1),
Madul INT
)
DECLARE @sql NVARCHAR(1000)
SET @sql = 'INSERT INTO #temp ([Madul]) SELECT [Madul] FROM Du_lich ' + @Where + ' ORDER BY ' + @OrderBy + ' ' + @OrderDirection
EXEC (@sql)
SELECT @TotalRecords = COUNT(*) FROM #Temp
SELECT
	dl.Madul,
	dl.Madden,
	dl.Masp
FROM #Temp AS T JOIN Du_lich AS dl ON T.Madul=dl.Madul
WHERE
(@PageIndex = 0)
OR
(T.IDTemp > (@PageIndex - 1) * @PageSize AND
T.IDTemp <= @PageIndex * @PageSize)
DROP TABLE #temp
--------------------------
create table Diem_den
(
	Madden int identity(1,1),
	Diemden nvarchar(255),
	primary key (Madden)
)
---------------INSERT INTO -----
CREATE PROCEDURE dbo.Diem_den_Insert
	@Diemden nvarchar(255)
AS
INSERT INTO Diem_den VALUES (
	@Diemden
)
RETURN
-----------UPDATE----
CREATE PROCEDURE dbo.Diem_den_Update
	@Madden int,
	@Diemden nvarchar(255)
AS
UPDATE Diem_den SET
	Diemden = @Diemden
WHERE Madden = @Madden
RETURN
--------DROP----
drop procedure dbo.Diem_den_Update
-------------DELETE----------
CREATE PROCEDURE dbo.Diem_den_Delete
@Madden int
AS
declare @tong Int
select @tong = count (*) from Du_lich where Madden = @Madden
if @tong = 0
begin
delete from Diem_den where Madden = @Madden
end
RETURN
-------------------------------GET----------
CREATE PROCEDURE dbo.Diem_den_Get
@Where NVARCHAR(255),
@PageIndex INT,
@PageSize INT,
@OrderBy NVARCHAR(25),
@OrderDirection NVARCHAR(4),
@TotalRecords INT OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
CREATE TABLE #Temp (
IDTemp INT IDENTITY(1,1),
Madden INT
)
DECLARE @sql NVARCHAR(1000)
SET @sql = 'INSERT INTO #temp ([Madden]) SELECT [Madden] FROM Diem_den ' + @Where + ' ORDER BY ' + @OrderBy + ' ' + @OrderDirection
EXEC (@sql)
SELECT @TotalRecords = COUNT(*) FROM #Temp
SELECT
	dd.Madden,
	dd.Diemden
FROM #Temp AS T JOIN Dien_den AS dd ON T.Madden=dd.Madden
WHERE
(@PageIndex = 0)
OR
(T.IDTemp > (@PageIndex - 1) * @PageSize AND
T.IDTemp <= @PageIndex * @PageSize)
DROP TABLE #temp
--------------------------
create table Deal
(
	Madeal int identity(1,1),
	Masp int,
	Tinhtrang bit,
	primary key (Madeal),
constraint fk_deal_sanp foreign key (Masp) references San_pham(Masp)
)
---------------INSERT INTO -----
CREATE PROCEDURE dbo.Deal_Insert
	@Masp int,
	@Tinhtrang bit
AS
INSERT INTO Deal VALUES (
	@Masp,
	@Tinhtrang
)
RETURN
-----------UPDATE----
CREATE PROCEDURE dbo.Deal_Update
	@Madeal int,
	@Masp int,
	@Tinhtrang bit
AS
UPDATE Deal SET
	Masp = @Masp,
	Tinhtrang = @Tinhtrang
WHERE Madeal = @Madeal
RETURN
--------DROP----
drop procedure dbo.Deal_Update
-------------DELETE----------chua chay
CREATE PROCEDURE dbo.Deal_Delete
@Madeal int
AS
declare @tong Int
select @tong = count (*) from San_pham where Madeal = @Madeal
if @tong = 0
begin
delete from Deal where Madeal = @Madeal
end
RETURN
-------------------------------GET----------
CREATE PROCEDURE dbo.Deal_Get
@Where NVARCHAR(255),
@PageIndex INT,
@PageSize INT,
@OrderBy NVARCHAR(25),
@OrderDirection NVARCHAR(4),
@TotalRecords INT OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
CREATE TABLE #Temp (
IDTemp INT IDENTITY(1,1),
Madeal INT
)
DECLARE @sql NVARCHAR(1000)
SET @sql = 'INSERT INTO #temp ([Madeal]) SELECT [Madeal] FROM Deal ' + @Where + ' ORDER BY ' + @OrderBy + ' ' + @OrderDirection
EXEC (@sql)
SELECT @TotalRecords = COUNT(*) FROM #Temp
SELECT
	de.Madeal,
	de.Masp,
	de.Tinhtrang
FROM #Temp AS T JOIN Deal AS de ON T.Madeal=de.Madeal
WHERE
(@PageIndex = 0)
OR
(T.IDTemp > (@PageIndex - 1) * @PageSize AND
T.IDTemp <= @PageIndex * @PageSize)
DROP TABLE #temp
--------------------------